import csv
import json
from datetime import datetime, timedelta
from django.http import HttpResponse, JsonResponse
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
from app01.models import ABSO_order, ABSO_order_types
from rest_framework.views import APIView
from app01.Baseview import MsgView, BasedAPIView
from django import forms
from django.db.models import Q, Count
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage
from django.db.models import Sum
from datetime import datetime, timedelta


def get_last_month_dates(current_start, current_end):
    last_month_start = current_start - timedelta(days=30)
    last_month_end = current_end - timedelta(days=30)
    return last_month_start, last_month_end


class AbsoGetOrder_Api(BasedAPIView):
    # 类级别的全局映射字典和索引计数器
    user_type_mapping = {}
    index_counter = 0

    def get_page_all(self, request):
        uid = request.GET.get('id', None)
        pageindex = int(request.GET.get("page", 1))
        pagesize = int(request.GET.get("pagesize", 10))
        user_name = request.GET.get("user_name", None)
        code = request.GET.getlist("code", None)
        user_type = request.GET.get("user_type", None)
        createTime = request.GET.getlist("createdTime[]", [])
        user_address = request.GET.get("user_address", None)
        status = request.GET.get("status", None)
        query = Q()
        if user_type:
            query &= Q(type_id=user_type)
        if user_address is not None:
            query &= Q(user_address__icontains=user_address)
        if user_name is not None:
            query &= Q(user_name__icontains=user_name)
        if status is not None:
            if status is not None:
                status_list = [int(s) for s in status.split(',')]  # 将字符串分割成列表并转换为整数
            else:
                status_list = [2, 3, 5]
            query &= Q(status__in=status_list)
        if code is not None:
            for single_type_code in code:
                query |= Q(type_order__iexact=single_type_code)
        if createTime:
            start_time_str = createTime[0]
            end_time_str = createTime[1]
            start_time = datetime.strptime(start_time_str, '%Y-%m-%d') if start_time_str else None
            end_time = datetime.strptime(end_time_str, '%Y-%m-%d') if end_time_str else None
            end_time_exclusive = end_time + timedelta(days=1)
            query &= Q(createTime__gte=start_time, createTime__lt=end_time_exclusive)
        else:
            start_time = datetime.today().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
            end_time = datetime.today() + timedelta(days=1)
        orders = ABSO_order.objects.filter(query).order_by('-id')
        ###分页
        page = Paginator(orders, pagesize)
        try:
            page_orders = page.page(pageindex)
        except page:
            # 如果请求的页面超出了范围，返回空列表
            page_orders = []
        # 统计总数
        total1 = page.count
        address_counts_queryset = ABSO_order.objects.filter(query) \
            .values('user_address', 'user_name', 'accountPhone', 'type_id') \
            .annotate(count=Count('id'), total_amount=Sum('totalprice')).order_by('-count')

        user_total_amount = ABSO_order.objects.filter(query).aggregate(Sum('totalprice'))['totalprice__sum'] or 0
        paginator = Paginator(address_counts_queryset, pagesize)
        total2 = paginator.count
        add_list = []
        for items in page_orders:
            add_dic = {
                "user_name": items.user_name,
                "accountPhone": items.accountPhone,
                "user_type": items.type_id,
                "user_address": items.user_address,
                "createTime": items.createTime
            }
            add_list.append(add_dic)
        total_list = []
        for items in list(paginator.page(pageindex)):
            add_dic = {
                "user_name": items['user_name'],
                "accountPhone": items['accountPhone'],
                "user_type": items['type_id'],
                "user_address": items['user_address'],
                "count": items['count'],
                "total_amount": items['total_amount'],
            }
            total_list.append(add_dic)

        data = {
            "order_total": total1,
            "total": total2,
            "total_amount": user_total_amount,
            "data": total_list,
        }
        return super().success(data=data)
        # 查询订单
        # orders = ABSO_order.objects.filter(query).order_by('-id')
        #
        # # 分页
        # page = Paginator(orders, pagesize)
        # try:
        #     page_orders = page.page(pageindex)
        # except page:
        #     # 如果请求的页面超出了范围，返回空列表
        #     page_orders = []
        # # 统计总数
        # total1 = page.count
        #
        # # 统计地址相关的信息
        # address_counts_queryset = ABSO_order.objects.filter(query) \
        #     .values('user_address', 'user_name', 'accountPhone', 'type_id') \
        #     .annotate(count=Count('id'), total_amount=Sum('totalprice')).order_by('-count')
        #
        # # 计算总金额
        # user_total_amount = ABSO_order.objects.filter(query).aggregate(Sum('totalprice'))['totalprice__sum'] or 0
        #
        # # 计算当前月和上个月的差异
        # current_month_query = Q(createTime__gte=start_time, createTime__lt=end_time)
        # last_month_start, last_month_end = get_last_month_dates(start_time, end_time)
        # last_month_query = Q(createTime__gte=last_month_start, createTime__lt=last_month_end)
        #
        # if user_type:
        #     current_month_query &= Q(type_id=user_type)
        #     last_month_query &= Q(type_id=user_type)
        #
        # current_month_orders = ABSO_order.objects.filter(query & current_month_query) \
        #     .values('user_name', 'accountPhone', 'type_id', 'user_address') \
        #     .annotate(totalprice=Sum('totalprice'), counts=Count('id'))
        #
        # last_month_orders = ABSO_order.objects.filter(query & last_month_query) \
        #     .values('user_name', 'accountPhone', 'type_id', 'user_address') \
        #     .annotate(totalprice=Sum('totalprice'), counts=Count('id'))
        #
        # # 将当前月和上个月的数据合并，并计算差异
        # combined_summary = []
        # for item in address_counts_queryset:
        #     current_item = next((c for c in current_month_orders if
        #                          c['user_name'] == item['user_name'] and
        #                          c['accountPhone'] == item['accountPhone'] and
        #                          c['type_id'] == item['type_id'] and
        #                          c['user_address'] == item['user_address']), None)
        #     last_item = next((l for l in last_month_orders if
        #                       l['user_name'] == item['user_name'] and
        #                       l['accountPhone'] == item['accountPhone'] and
        #                       l['type_id'] == item['type_id'] and
        #                       l['user_address'] == item['user_address']), None)
        #
        #     current_totalprice = current_item['totalprice'] if current_item else 0
        #     last_totalprice = last_item['totalprice'] if last_item else 0
        #     price_difference = current_totalprice - last_totalprice
        #
        #     current_counts = current_item['counts'] if current_item else 0
        #     last_counts = last_item['counts'] if last_item else 0
        #     count_difference = current_counts - last_counts
        #
        #     combined_summary.append({
        #         "user_name": item['user_name'],
        #         "accountPhone": item['accountPhone'],
        #         "user_type": item['type_id'],
        #         "user_address": item['user_address'],
        #         "count": item['count'],
        #         "total_amount": item['total_amount'],
        #         "current_counts": current_counts,
        #         "last_counts": last_counts,
        #         "count_difference": count_difference,
        #         "current_totalprice": current_totalprice,
        #         "last_totalprice": last_totalprice,
        #         "price_difference": price_difference
        #     })
        #
        # # 构建响应数据
        # add_list = []
        # for item in page_orders:
        #     add_dic = {
        #         "user_name": item.user_name,
        #         "accountPhone": item.accountPhone,
        #         "user_type": item.type_id,
        #         "user_address": item.user_address,
        #         "createTime": item.createTime.strftime('%Y-%m-%d %H:%M:%S') if item.createTime else None,
        #     }
        #     add_list.append(add_dic)
        #
        # data = {
        #     "order_total": total1,
        #     "total": len(combined_summary),
        #     "total_amount": user_total_amount,
        #     "data": combined_summary,
        # }
        #
        # return super().success(data=data)

    def get_types(self, request):
        types = ABSO_order_types.objects.all()
        data_dic = [
            {
                'id': item.id,
                'name': item.name
            }
            for item in types
        ]
        return super().success(data=data_dic)

    def get_total(self, request):
        # default_dates = get_last_month_dates()
        createTime = request.GET.getlist("createdTime[]", )  ###default=default_dates
        user_type = request.GET.get("user_type", None)
        query = Q()
        if createTime:
            query &= Q(createTime__gte=createTime[0], createTime__lt=createTime[1])
        if user_type:
            query &= Q(type_id=user_type)
        query &= Q(status__in=[2, 3, 4, 5])
        # query &= Q(status__in=[5])
        orders = ABSO_order.objects.filter(query) \
            .values('type_id') \
            .annotate(totalprice=Sum('totalprice'),
                      counts=Count('id')) \
            .order_by('-totalprice')

        # 获取会员类型名称
        type_names = ABSO_order_types.objects.filter(id__in=[item['type_id'] for item in orders]) \
            .values('id', 'name')
        type_name_dict = {t['id']: t['name'] for t in type_names}

        data_list = []
        for item in orders:
            data_list.append(
                {
                    'id': item['type_id'],
                    'counts': item['counts'],
                    'user_type': type_name_dict.get(item['type_id']),
                    'totalprice': item['totalprice']
                }
            )
        data = {
            "data": data_list
        }
        return super().success(data=data)
        # orders = ABSO_order.objects.filter(query) \
        #     .values('type_id') \
        #     .annotate(totalprice=Sum('totalprice'),
        #               counts=Count('id')) \
        #     .order_by('-totalprice')
        #
        # # 获取会员类型名称
        # type_names = ABSO_order_types.objects.filter(id__in=[item['type_id'] for item in orders]) \
        #     .values('id', 'name')
        # type_name_dict = {t['id']: t['name'] for t in type_names}
        #
        # # 计算当前月和上个月的差异
        # current_month_start = datetime.today().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        # last_month_start = (current_month_start - timedelta(days=1)).replace(day=1)
        # last_month_end = current_month_start - timedelta(days=1)
        #
        # current_month_query = Q(createTime__gte=current_month_start, createTime__lt=datetime.today())
        # last_month_query = Q(createTime__gte=last_month_start, createTime__lte=last_month_end)
        #
        # if user_type:
        #     current_month_query &= Q(type_id=user_type)
        #     last_month_query &= Q(type_id=user_type)
        #
        # current_month_orders = ABSO_order.objects.filter(query & current_month_query) \
        #     .values('type_id') \
        #     .annotate(totalprice=Sum('totalprice'), counts=Count('id'))
        #
        # last_month_orders = ABSO_order.objects.filter(query & last_month_query) \
        #     .values('type_id') \
        #     .annotate(totalprice=Sum('totalprice'), counts=Count('id'))
        #
        # # 将当前月和上个月的数据合并，并计算差异
        # combined_summary = []
        # for item in orders:
        #     current_item = next((c for c in current_month_orders if c['type_id'] == item['type_id']), None)
        #     last_item = next((l for l in last_month_orders if l['type_id'] == item['type_id']), None)
        #
        #     current_totalprice = current_item['totalprice'] if current_item else 0
        #     last_totalprice = last_item['totalprice'] if last_item else 0
        #     price_difference = current_totalprice - last_totalprice
        #
        #     current_counts = current_item['counts'] if current_item else 0
        #     last_counts = last_item['counts'] if last_item else 0
        #     count_difference = current_counts - last_counts
        #
        #     combined_summary.append({
        #         'id': item['type_id'],
        #         'counts': item['counts'],
        #         'user_type': type_name_dict.get(item['type_id']),
        #         'totalprice': item['totalprice'],
        #         'current_counts': current_counts,
        #         'last_counts': last_counts,
        #         'count_difference': count_difference,
        #         'current_totalprice': current_totalprice,
        #         'last_totalprice': last_totalprice,
        #         'price_difference': price_difference
        #     })
        #
        # data = {
        #     "data": combined_summary
        # }
        # return super().success(data=data)

    def export_total(self, request):
        data = json.loads(request.body)
        user_name = data.get("user_name", None)
        code = data.get("code", None)
        user_type = data.get("user_type", None)
        createTime = data.get("createdTime[]", [])
        user_address = data.get("user_address", None)
        status = data.get("status", 5)
        query = Q()
        if user_type is not None:
            query &= Q(user_type_name__icontains=user_type)
        if user_address is not None:
            query &= Q(user_address__icontains=user_address)
        if user_name is not None:
            query &= Q(user_name__icontains=user_name)
        if status is not None:
            query &= Q(status=status)
        if code is not None:
            for single_type_code in code:
                query |= Q(type_order__iexact=single_type_code)
        if createTime:
            start_time_str = createTime[0]
            end_time_str = createTime[1]
            start_time = datetime.strptime(start_time_str, '%Y-%m-%d') if start_time_str else None
            end_time = datetime.strptime(end_time_str, '%Y-%m-%d') if end_time_str else None
            end_time_exclusive = end_time + timedelta(days=1)
            query &= Q(createTime__gte=start_time, createTime__lt=end_time_exclusive)

        address_counts_queryset = ABSO_order.objects.filter(query) \
            .values('user_address', 'user_name', 'accountPhone', 'user_type_name') \
            .annotate(count=Count('id'), total_amount=Sum('totalprice')).order_by('-count')
        # print('address_counts_queryset', address_counts_queryset)

        # 创建工作簿和工作表
        wb = Workbook()
        ws = wb.active
        ws.title = "收货地址汇总"

        # 设置表头
        headers = ['姓名', '会员类型', '联系电话', '地址', '订单数量', '总金额']
        column_widths = [20, 20, 20, 60, 10, 25]
        for col_num, (header, width) in enumerate(zip(headers, column_widths), 1):
            col_letter = get_column_letter(col_num)
            cell = ws.cell(row=1, column=col_num, value=header)
            cell.alignment = Alignment(horizontal='center')
            ws.column_dimensions[col_letter].width = width  # 设置每列的宽度

        # 写入数据
        for row_num, entry in enumerate(address_counts_queryset, 2):
            ws.cell(row=row_num, column=1, value=entry['user_name'])
            ws.cell(row=row_num, column=2, value=entry['user_type_name'])
            ws.cell(row=row_num, column=3, value=entry['accountPhone'])
            ws.cell(row=row_num, column=4, value=entry['user_address'])
            ws.cell(row=row_num, column=5, value=entry['count'])
            ws.cell(row=row_num, column=6, value=entry['total_amount'])

        # 创建 HTTP 响应对象
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = 'attachment; filename="收货地址汇总.xlsx"'

        # # 保存工作簿到响应对象
        wb.save(response)
        return response
